package org.example.methods;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class Methods {
    private static final String URL = "jdbc:mysql://192.168.8.115:3306/attendance";
    private static final String USER = "root";
    private static final String PASSWORD = "root";

    public static ArrayList<Integer> table1() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = "SELECT * FROM table1";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<Integer> list = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            int A = resultSet.getInt("A");
            int L = resultSet.getInt("L");

            list.add(A);
            list.add(L);
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();
        return list;
    }

    public static HashMap<String, ArrayList<?>> table2() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = "SELECT * FROM table2 limit 5";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> list_name = new ArrayList<>();
        ArrayList<Integer> list_A = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            list_name.add(resultSet.getString("name"));
            list_A.add(resultSet.getInt("A"));
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();

        HashMap<String, ArrayList<?>> map = new HashMap<>();
        map.put("name", list_name);
        map.put("A", list_A);

        return map;
    }

    public static HashMap<String, ArrayList<?>> table3() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = "SELECT * FROM table3 limit 3";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> list_name = new ArrayList<>();
        ArrayList<Integer> list_L = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            list_name.add(resultSet.getString("name"));
            list_L.add(resultSet.getInt("L"));
        }
        resultSet.close();
        preparedStatement.close();
        connection.close();

        HashMap<String, ArrayList<?>> map = new HashMap<>();
        map.put("name", list_name);
        map.put("L", list_L);

        return map;
    }

    public static HashMap<String, ArrayList<?>> CF1() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = "SELECT name1, SUM(CONVERT(related, DECIMAL(10, 6))) AS related_sum\n" +
                "FROM CF\n" +
                "GROUP BY name1\n" +
                "ORDER BY related_sum DESC\n" +
                "LIMIT 6;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> list_name1 = new ArrayList<>();
        ArrayList<Double> list_related = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            list_name1.add(resultSet.getString("name1"));
            list_related.add(resultSet.getDouble("related_sum"));

        }

        resultSet.close();
        preparedStatement.close();
        connection.close();

        HashMap<String, ArrayList<?>> map = new HashMap<>();
        map.put("name1", list_name1);
        map.put("related", list_related);
        System.out.println(map);

        return map;
    }


    public static HashMap<String, ArrayList<?>> CF2(String name) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection(URL, USER, PASSWORD);
        String sql = String.format("SELECT * FROM CF WHERE name1='%s' ORDER BY related DESC LIMIT 3;", name);
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();

        ArrayList<String> list_name1 = new ArrayList<>();
        ArrayList<String> list_name2 = new ArrayList<>();
        ArrayList<Double> list_related = new ArrayList<>();
        // 处理结果集
        while (resultSet.next()) {
            list_name1.add(resultSet.getString("name1"));
            list_name2.add(resultSet.getString("name2"));
            double related = Double.parseDouble(resultSet.getString("related"));
            list_related.add(related);

        }

        resultSet.close();
        preparedStatement.close();
        connection.close();

        HashMap<String, ArrayList<?>> map = new HashMap<>();
        map.put("name1", list_name1);
        map.put("name2", list_name2);
        map.put("related", list_related);

        return map;
    }

}








